package utils;

import org.testng.annotations.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class utils_user {
    public static void main(String[] args) {

    }
    @Test
    public void textDML02(){
        PreparedStatement preparedStatement=null;
        Connection connection=null;
        ResultSet resultSet=null;
        String url="update db_1 set sal=sal-100 where id=1";
        String url1="update db_1 set sal=sal+100 where id=2";
        try {
            connection = utils.getconnection();
            connection.setAutoCommit(false);
            preparedStatement=connection.prepareStatement(url);
            preparedStatement.executeUpdate();

            preparedStatement=connection.prepareStatement(url1);
            preparedStatement.executeUpdate();

            connection.commit();

           /* while (resultSet.next()){
                String username = resultSet.getString("username");
                String pwd = resultSet.getString("pwd");
                System.out.println(username +"\t"+pwd+"\t");
            }*/
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            utils.close(null,preparedStatement,connection);
        }
    }



    @Test
    public void textDML01(){
        PreparedStatement preparedStatement=null;
        Connection connection=null;
        ResultSet resultSet=null;
        String url="select * from db_ where username=?";
        try {
            connection = utils.getconnection();
            preparedStatement=connection.prepareStatement(url);
            preparedStatement.setString(1,"jack");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                String username = resultSet.getString("username");
                String pwd = resultSet.getString("pwd");
                System.out.println(username +"\t"+pwd+"\t");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            utils.close(resultSet,preparedStatement,connection);
        }
    }



    @Test
    public void textDML(){
        PreparedStatement preparedStatement=null;
        Connection connection=null;
        String url="update db_ set username=? where username=?";
        try {
            connection = utils.getconnection();
            preparedStatement=connection.prepareStatement(url);
            preparedStatement.setString(2,"tom");
            preparedStatement.setString(1,"jack");
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            utils.close(null,preparedStatement,connection);
        }
    }
}
